﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;


namespace SQLTask2
{
    

    public partial class Form1 : Form
    {
        public MySqlConnection conn = null;
        private string connStr = null;
        private MySqlDataAdapter da = null;
        private DataTable ds = null;

        private string[] queries = {//Fill main grid  r.Sell*t.Quantity
                                    @"SELECT
                                           t.Transaction_ID AS `ID`,
                                           t.Time AS `Время`,
                                           t.Date AS `Дата`,
                                           t.Quantity AS `Кол-во`,
                                           r.Buy AS `Покупка`,
                                           r.Sell AS `Продажа`,
                                           CASE
                                           WHEN t.Type='0' THEN
                                               r.Sell*t.Quantity
                                           ELSE
                                               r.Buy*t.Quantity
                                           END as `Сумма`,
							               f.Name AS `В1`,
							               s.Name AS `В2`
                                    FROM transactions t
                                    JOIN rates r ON t.Rate_ID=r.Rate_ID
                                    JOIN currencies f ON r.F_Currency_ID=f.Currency_ID
                                    JOIN currencies s ON r.S_Currency_ID=s.Currency_ID
                                    WHERE t.Bank_ID='{0}'",
                                    //Fill countries combobox
                                    @"SELECT Country_ID, Name FROM countries;",
                                    @"DELETE FROM banks WHERE banks.Bank_ID={0}"
                                   };




        public void AddChildren(TreeNode parent_node, int parent_id)
        {
            MySqlCommand command = null;
            if (parent_id == -1)
                command = new MySqlCommand("SELECT * FROM banks WHERE Parent_Bank_ID = 0 ORDER BY Bank_ID", conn);
            else
                command = new MySqlCommand("SELECT * FROM banks WHERE Parent_Bank_ID = " + parent_id.ToString() + " ORDER BY Bank_ID", conn);
            MySqlDataReader reader = command.ExecuteReader();
            List<TreeNode> nodes = new List<TreeNode>();
            while (reader.Read())
            {
                TreeNode newNode = new TreeNode();
                newNode.Text = reader["Name"].ToString();
                newNode.Tag = reader["Bank_ID"];
                nodes.Add(newNode);
            }
            reader.Close();
            foreach (TreeNode node in nodes)
            {
                AddChildren(node, Convert.ToInt32(node.Tag));
            }
            if (parent_node == null)
                this.treeView1.Nodes[0].Nodes.AddRange(nodes.ToArray());
            else
                parent_node.Nodes.AddRange(nodes.ToArray());
        }

        public void RefreshTreeView()
        {
            treeView1.Nodes.Clear();
            treeView1.Nodes.Add("Все банки");
            treeView1.Nodes[0].Tag = 0;
            treeView1.Focus();
            treeView1.SelectedNode = treeView1.Nodes[0];
            AddChildren(null, -1);
        }


        private void UpdateMainTable()
        {
            try
            {
                ds.Clear();
                da.Fill(ds);
                //dataGridView1.Columns[0].Width = 40;
                dataGridView1.Refresh();
            }
            catch (MySqlException sqlExc)
            {
                MessageBox.Show(sqlExc.Message);
            }
            
        }


        public Form1()
        {
            InitializeComponent();
            ds = new DataTable();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            CurrenciesListForm frm = new CurrenciesListForm(conn);
            frm.ShowDialog();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            try
            {
                connStr = "server=localhost;userid=root;password=test;database=test";
                conn = new MySqlConnection(connStr);
                conn.Open();
                RefreshTreeView();
                dataGridView1.DataSource = ds;
            }
            catch (MySqlException exc)
            {
                MessageBox.Show(exc.Message);
                this.Close();
            }
        }

        private void Form1_FormClosing(object sender, FormClosingEventArgs e)
        {
            conn.Close();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            CountriesForm frm = new CountriesForm(conn);
            frm.ShowDialog();
        }

        private void button3_Click(object sender, EventArgs e)
        {
            if (treeView1.SelectedNode != null)
            {
                int parent_id = (int)treeView1.SelectedNode.Tag;
                BanksForm frm;
                frm = new BanksForm(this, conn, false, parent_id);
                frm.ShowDialog();
            }
        }

        private void treeView1_AfterSelect(object sender, TreeViewEventArgs e)
        {
            treeView1.Cursor = Cursors.WaitCursor;
            if (treeView1.SelectedNode.Level != 0)
            {
                dataGridView1.Visible = true;
                groupBox1.Visible = true;
                try
                {
                    string qry = String.Format(queries[0], treeView1.SelectedNode.Tag);
                    da = new MySqlDataAdapter(qry, conn);
                    UpdateMainTable();
                    using (MySqlCommand command = new MySqlCommand("SELECT b.Name, b.Location, c.Name AS `CName` FROM banks b JOIN countries c ON b.Country_ID=c.Country_ID WHERE b.Bank_ID ='" + treeView1.SelectedNode.Tag + "'", conn))
                    {
                        using (MySqlDataReader reader = command.ExecuteReader())
                        {
                            reader.Read();
                            labelBankName.Text = reader["Name"].ToString();
                            labelBankLocation.Text = reader["Location"].ToString();
                            labelBankCountry.Text = reader["CName"].ToString();
                        }
                    }
                }
                catch (MySqlException sqlExc)
                {
                    MessageBox.Show(sqlExc.Message);
                }
            }
            else
            {
                dataGridView1.Visible = false;
                groupBox1.Visible = false;
            /*    try
                {
                    string qry = queries[2];
                    da = new MySqlDataAdapter(qry, conn);
                    UpdateMainTable();
                }
                catch (MySqlException sqlExc)
                {
                    MessageBox.Show(sqlExc.Message);
                }*/

            }
            treeView1.Cursor = Cursors.Default;

        }

        private void button4_Click(object sender, EventArgs e)
        {
            RatesForm frm = new RatesForm(conn);
            frm.ShowDialog();
        }

        private void toolButtonGraphs_Click(object sender, EventArgs e)
        {
            GraphsForm frm = new GraphsForm(conn);
            frm.ShowDialog();
        }

        private void button1_Click_1(object sender, EventArgs e)
        {
            RefreshTreeView();
        }

        private void buttonDeleteBank_Click(object sender, EventArgs e)
        {
            if (treeView1.SelectedNode != null)
            {
                try
                {
                    MySqlCommand cmd = new MySqlCommand(String.Format(queries[2], treeView1.SelectedNode.Tag), conn);
                    cmd.ExecuteNonQuery();
                }
                catch (MySqlException exc)
                {
                    MessageBox.Show(exc.Message);
                }
            }
        }

        private void button2_Click_1(object sender, EventArgs e)
        {
            if (treeView1.SelectedNode != null)
            {
                int parent_id = (int)treeView1.SelectedNode.Tag;
                BanksForm frm;
                frm = new BanksForm(this, conn, true, parent_id);
                frm.ShowDialog();
            }
        }

        private void button3_Click_1(object sender, EventArgs e)
        {
            SearchForm frm = new SearchForm(conn);
            frm.ShowDialog();
        }

        private void генераторТранзакцийToolStripMenuItem_Click(object sender, EventArgs e)
        {
            System.Diagnostics.Process.Start("TransactionsEmulator.exe");
        }

        private void генераторКурсовToolStripMenuItem_Click(object sender, EventArgs e)
        {
            System.Diagnostics.Process.Start("TransactionsEmulator.exe");
        }


    }
}
